package com.efesco.finance.gathering;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * inputGatheringDetailList_invoiceDetailDatasetListener
 */
public class inputGatheringDetailList_invoiceDetailDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
    String fesc_user_id="",sql="",foreign_enterprise_id="";
  	
  	DoradoContext context = DoradoContext.getContext();
    Object obj = context.getAttribute(context.SESSION, "User");
    if (obj != null) {
    	User user = (User) obj;
    	int roleType = user.getRoleType();
    	if (roleType == 2)  {
    		fesc_user_id=user.getForEmployeeId();
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		if (Tools.toStr(dataset.parameters().getString("type")).equals("1"))  {
        		sql = "SELECT cmp.single_company_id,cmp.company_chinese_name,i.invoice_code,i.invoice_amount,to_char(i.invoice_date,'yyyy-mm-dd') invoice_date," +
				"to_char(i.input_date,'yyyy-mm-dd') input_date,i.payment_type,i.if_effect,h.ought_month,o.ought_amount,o.payment_version_id," +
				"h.gathering_amount,h.rate_odds,h.gathering_memo,h.hr_invoice_detail_id,h.pre_month,h.payment_type oType,h.PAY_SEND_IDS,o.ought_receive_id " +
				"FROM " +  
				"s_hr_invoice_detail h,s_single_company cmp,s_invoice i,s_ought_receive o," +
				"s_company_businessman cbm,s_fe_emp_businesstype busi,s_fe_emp_scope scp " + 
				"WHERE "+ 
				"i.INVOICE_ID = h.INVOICE_ID and cmp.single_company_id=o.single_company_id and " + 
				"i.SINGLE_COMPANY_ID = cmp.SINGLE_COMPANY_ID AND h.ought_month=o.ought_month and " +
				"cmp.single_company_id=cbm.single_company_id and " + 
				"cbm.businessman_id=scp.fe_empid2 and cbm.business_id=busi.business_type_id " + 
				"and busi.employee_id=scp.fe_empid1 and cbm.start_date<=sysdate " + 
				"and (cbm.end_date is null or cbm.end_date>=sysdate) and " + 
				"scp.fe_empid1=" + fesc_user_id + " and cmp.FOREIGN_ENTERPRISE_ID = " + foreign_enterprise_id  +
				" and cbm.business_id=1 " +
				"and o.payment_type=h.payment_type and o.pay_send_ids=h.pay_send_ids and i.if_effect=1 ";
    		}
    		else  {
        		sql = "SELECT cmp.single_company_id,cmp.company_chinese_name,i.invoice_code,i.invoice_amount,to_char(i.invoice_date,'yyyy-mm-dd') invoice_date," +
				"to_char(i.input_date,'yyyy-mm-dd') input_date,i.payment_type,i.if_effect,h.ought_month,o.ought_amount,o.payment_version_id," +
				"h.gathering_amount,h.rate_odds,h.gathering_memo,h.hr_invoice_detail_id,h.pre_month,h.payment_type oType,h.PAY_SEND_IDS,o.ought_receive_id " +
				"FROM " +  
				"s_hr_invoice_detail h,s_single_company cmp,s_invoice i,s_ought_receive o," +
				"s_company_businessman cbm,s_fe_emp_businesstype busi,s_fe_emp_scope scp " + 
				"WHERE "+ 
				"i.INVOICE_ID = h.INVOICE_ID and cmp.single_company_id=o.single_company_id and " + 
				"i.SINGLE_COMPANY_ID = cmp.SINGLE_COMPANY_ID AND h.pre_month=o.ought_month and " +
				"cmp.single_company_id=cbm.single_company_id and " + 
				"cbm.businessman_id=scp.fe_empid2 and cbm.business_id=busi.business_type_id " + 
				"and busi.employee_id=scp.fe_empid1 and cbm.start_date<=sysdate " + 
				"and (cbm.end_date is null or cbm.end_date>=sysdate) and " + 
				"scp.fe_empid1=" + fesc_user_id + " and cmp.FOREIGN_ENTERPRISE_ID = " + foreign_enterprise_id  +
				" and cbm.business_id=1 " +
				"and o.payment_type=h.payment_type and o.pay_send_ids=h.pay_send_ids and i.if_effect=1 ";
    		}

    	}
    	else  {
    		if (Tools.toStr(dataset.parameters().getString("type")).equals("1"))  {
    			sql = "SELECT cmp.single_company_id,cmp.company_chinese_name,i.invoice_code,i.invoice_amount,to_char(i.invoice_date,'yyyy-mm-dd') invoice_date," +
				"i.payment_type,i.if_effect,h.ought_month,o.ought_amount,o.payment_version_id," +
				"h.gathering_amount,h.rate_odds,h.gathering_memo,h.hr_invoice_detail_id,h.pre_month,h.payment_type oType,h.PAY_SEND_IDS,o.ought_receive_id " +
				"FROM " +  
				"s_hr_invoice_detail h,s_single_company cmp,s_invoice i,s_ought_receive o " +
				"WHERE "+ 
				"i.INVOICE_ID = h.INVOICE_ID and cmp.single_company_id=o.single_company_id and " + 
				"i.SINGLE_COMPANY_ID = cmp.SINGLE_COMPANY_ID AND h.ought_month=o.ought_month " +
				"and o.payment_type=h.payment_type and o.pay_send_ids=h.pay_send_ids and i.if_effect=1 ";
    		}
    		else {
    			sql = "SELECT cmp.single_company_id,cmp.company_chinese_name,i.invoice_code,i.invoice_amount,to_char(i.invoice_date,'yyyy-mm-dd') invoice_date," +
				"i.payment_type,i.if_effect,h.ought_month,o.ought_amount,o.payment_version_id," +
				"h.gathering_amount,h.rate_odds,h.gathering_memo,h.hr_invoice_detail_id,h.pre_month,h.payment_type oType,h.PAY_SEND_IDS,o.ought_receive_id " +
				"FROM " +  
				"s_hr_invoice_detail h,s_single_company cmp,s_invoice i,s_ought_receive o " +
				"WHERE "+ 
				"i.INVOICE_ID = h.INVOICE_ID and cmp.single_company_id=o.single_company_id and " + 
				"i.SINGLE_COMPANY_ID = cmp.SINGLE_COMPANY_ID AND h.pre_month=o.ought_month  " +
				"and o.payment_type=h.payment_type and o.pay_send_ids=h.pay_send_ids and i.if_effect=1 ";
    		}
    	}
    	if (!Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("null")	)  {
    		sql = sql + " and cmp.single_company_id = " + dataset.parameters().getString("singleCompanyId");
    	}
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("oughtMonth")).equals("null")	)  {
        	sql = sql + " and h.ought_month = '" + dataset.parameters().getString("oughtMonth")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("invoiceCode")).equals("")
            && !Tools.toStr(dataset.parameters().getString("invoiceCode")).equals("null")	)  {
            sql = sql + " and i.invoice_code = '" + dataset.parameters().getString("invoiceCode")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("oType")).equals(""))  {
        	sql = sql + " and h.payment_type = '" + dataset.parameters().getString("oType")+"'";
        }
   	
    	sql = sql + " order by o.ought_month,cmp.COMPANY_CHINESE_NAME ";
    	SqlDataset sqlDataset=(SqlDataset)dataset;
    	sqlDataset.setSql(sql);
    }
    return true;
  }

}
